package com.shengruan.admin.jdbc;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import com.shengruan.admin.vo.Article;
import com.shengruan.framework.jdbc.BaseJdbc;
import com.shengruan.framework.jdbc.BeanHandler;
import com.shengruan.framework.jdbc.BeanListHandler;

public class ArticleJdbc  extends BaseJdbc{
	protected static Log log = LogFactory.getLog(ArticleJdbc.class);
	/**
	 * 保存
	 */
	public void save(Article article)
	{

		Connection conn = this.getConnection();
		String sql = "insert into Article (" +
		"type,title,content,postTime,pic)" +
		" values(?,?,?,?,?)";
		try {
			this.execute(conn, sql,new Object[]{article.getType(),article.getTitle(),article.getContent(),article.getPostTime(),article.getPic()});
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			this.close(conn);
		}

	}

	/**
	 * 更新
	 */
	public void update(Article article){
		Connection conn = this.getConnection();
		String sql = "update Article set type=?,title=?,content=?,postTime=?,pic=? where id=?";
		try {
			this.execute(conn, sql, article.getType(),article.getTitle(),article.getContent(),article.getPostTime(),article.getPic(),article.getId());
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			this.close(conn);
		}
	}

	/***
	 * 根据ID删除
	 */
	public void deleteById(int id)
	{
		Connection conn = this.getConnection();
	
		String sql = "delete from Article where id = ? ";
		try {
			this.execute(conn, sql, id);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			this.close(conn);
		}
	}

	/***
	 * 获得个数
	 * @return
	 */
	public  int getRows(int type)
	{
		Connection conn = this.getConnection();
		String sql = "select count(id) from Article where type = ?";
		int count = 0;
		try {
			count = this.getRows(conn, sql, type);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			this.close(conn);
		}
		return count;	
	}

    /***
     * 获得个数
     * @return
     */
    public  int getRows()
    {
        Connection conn = this.getConnection();
        String sql = "select count(id) from Article";
        int count = 0;
        try {
            count = this.getRows(conn, sql);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            this.close(conn);
        }
        return count;
    }

    /***
     * 获得文章列表
     * @param pageSize
     * @param currentPage
     * @return
     */
    public  List<Article> findWithPage(int currentPage,int pageSize)
    {
        List<Article> articleList = new ArrayList<Article>();
        Connection conn = this.getConnection();
        String sql = "select id,type,title,postTime,pic from Article order by postTime desc limit ?,?";
        try {
            int start = (currentPage-1)*pageSize;
            articleList = this.query(conn, sql, new BeanListHandler<Article>(Article.class),start,pageSize);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            this.close(conn);
        }
        return articleList;
    }
    
    public List<Article> getArticleByTop(int type,int top) {
        List<Article> articleList = new ArrayList<Article>();
        Connection connection = this.getConnection();
        try {
            articleList = this.query(
                    connection,
                    "SELECT id,type,title,postTime FROM ARTICLE WHERE TYPE = ? ORDER BY POSTTIME DESC LIMIT ?",
                    new BeanListHandler<Article>(Article.class),
                    type,
                    top
            );
        }catch (SQLException e) {
            e.printStackTrace();
        } finally {
            this.close(connection);
        }
        return articleList;
    }

	/***
	 * 获得文章列表
	 * @param pageSize
	 * @param currentPage
	 * @return
	 */
	public  List<Article> findWithPage(int currentPage,int pageSize,int type)
	{
		List<Article> articleList = new ArrayList<Article>();
		Connection conn = this.getConnection();
		String sql = "select id,type,title,content,postTime,pic from Article where type = ? order by postTime desc limit ?,?";
		try {
			int start = (currentPage-1)*pageSize;
			articleList = this.query(conn, sql, new BeanListHandler<Article>(Article.class), type ,start,pageSize);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			this.close(conn);
		}
		return articleList;
	}

    /**
     *
     * @return
     */
    public List<Article> findAll() {
        List<Article> articleList = new ArrayList<Article>();
        Connection conn = this.getConnection();
		String sql = "select id,type,title,postTime,pic from  Article order by postTime desc";
		try {
			articleList = this.query(conn, sql, new BeanListHandler<Article>(Article.class));
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			this.close(conn);
		}
		return articleList;
    }


	/***
	 * 根据用ID获得
	 */
	public Article getById(int id){
		Connection conn = this.getConnection();
		
		String sql = "select * from Article where id = ?";
		Article article = null;
		
		try {
			article = this.query(conn, sql, new BeanHandler<Article>(Article.class),id);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			this.close(conn);
		}
		return article;
	}

}
